CMSC 408 - Deliverable 12

Published

December 17, 2024

Financial Data Manager Project

Project Overview and Key Deliverables

The purpose of this project is to aid investors in making the best investment choices in the stock market. Our database provides the necessary financial data that an investor can find highly useful to evaluate a company’s financial health and wealth.

Problem Description

  • Problem domain: Creating a database with this data would address a problem faced by investors and financial analysts. Companies can have different means of reporting which makes it inefficient and time-consuming to look at.

  • Need: A database is needed to store and organize financial information and is useful for quicker decision-making processes and better management of all the existing data.

Entity-relationship diagrams

Chen diagram

ER Company Company publishes publishes Company--publishes 1 issues issues Company--issues 1 records records Company--records 1 flows flows Company--flows 1 reports reports Company--reports 1 Income_Stmt Income_Stmt Stock Stock Balance_Sheet Balance_Sheet Cash_Flow Cash_Flow Earnings Earnings name name name--Company sector sector sector--Company industry industry industry--Company ticker ticker ticker--Company ticker--Income_Stmt ticker--Stock ticker--Balance_Sheet ticker--Cash_Flow ticker--Earnings IPO_date IPO_date IPO_date--Stock price price price--Stock stmt_id1 stmt_id1 stmt_id1--Income_Stmt stmt_id2 stmt_id2 stmt_id2--Balance_Sheet stmt_id3 stmt_id3 stmt_id3--Cash_Flow fiscal_end fiscal_end fiscal_end--Income_Stmt fiscal_end--Balance_Sheet fiscal_end--Cash_Flow rev rev rev--Income_Stmt cost_rev cost_rev cost_rev--Income_Stmt gross gross gross--Income_Stmt op_inc op_inc op_inc--Income_Stmt net_inc net_inc net_inc--Income_Stmt inc_before_tax inc_before_tax inc_before_tax--Income_Stmt ebit ebit ebit--Income_Stmt ebitda ebitda ebitda--Income_Stmt assets assets assets--Balance_Sheet curr_assets curr_assets curr_assets--Balance_Sheet cash_eq cash_eq cash_eq--Balance_Sheet acc_rec acc_rec acc_rec--Balance_Sheet debt debt debt--Balance_Sheet lt_debt lt_debt lt_debt--Balance_Sheet tang_assets tang_assets tang_assets--Balance_Sheet wc wc wc--Balance_Sheet invest_cap invest_cap invest_cap--Balance_Sheet tang_bv tang_bv tang_bv--Balance_Sheet cap cap cap--Balance_Sheet shares shares shares--Balance_Sheet equity equity equity--Balance_Sheet retained retained retained--Balance_Sheet com_eq com_eq com_eq--Balance_Sheet op_cf op_cf op_cf--Cash_Flow cap_exp cap_exp cap_exp--Cash_Flow fcf fcf fcf--Cash_Flow div_paid div_paid div_paid--Cash_Flow earnings_id earnings_id earnings_id--Earnings fiscal_end_date fiscal_end_date fiscal_end_date--Earnings eps eps eps--Earnings publishes--Income_Stmt N issues--Stock N records--Balance_Sheet N flows--Cash_Flow N reports--Earnings N
Figure 1: Financial Data Manager in Chen Notation

Crows foot diagram:

erDiagram
    COMPANY {
        string ticker
        string name
        string sector
        string industry
    }

    INCOME_STATEMENT {
        int statement_id1
        string ticker
        date fiscal_year_end
        float total_revenue
        float cost_of_revenue
        float gross_profit
        float operating_income
        float Net_income_common_stockholders
        float income_before_tax
        float ebit
        float ebitda
    }

    STOCK {
        string ticker
        int company_id
        float price
        date IPO_date
    }

    BALANCE_SHEET {
        string ticker
        int statement_id2
        date fiscal_year_end
        float total_assets
        float current_assets
        float cash
        float cash_and_cash_equivalents
        float accounts_receivables 
        float total_debt
        float net_tangible_assets
        float long_term_debt
        float working_capital
        float invested_capital
        float tangible_book_value
        float total_capitalization
        int shares_issued
        float stockholders_equity
        float retained_earnings
        float common_stock_equity
    }

    CASH_FLOW {
        int statement_id3
        string ticker
        date fiscal_year_end
        float operating_cashflow
        float capital_expenditures
        float free_cash_flow
        float cash_dividends_paid
    }

    EARNINGS {
        int earnings_id
        string ticker
        int earnings_id
        int company_id
        date fiscal_date_end
        float reported_eps
    }

    COMPANY ||--o{ INCOME_STATEMENT : publishes
    COMPANY ||--o{ STOCK : issues
    COMPANY ||--o{ BALANCE_SHEET : records
    COMPANY ||--o{ CASH_FLOW : flows
    COMPANY ||--o{ EARNINGS : reports

Screenshots

Image 1 - Main Screen

This image displays the default page of our application. Main

Image 2 - Creating a New Record

This image shows the web interface screen for creating a new record. In this example, it is creating a new record in the ‘Company’ table. Create

Image 3 - Editing an Existing Record

This image shows the web interface screen for updating an existing record. In this example, it is editing ‘Eod_price’ attribute of a record in the ‘Stock’ table. Update

Image 4 - Deleting an Existing Record

This image shows that records can be deleted using its primary key. In this example, it is deleting a record from the ‘Stock’ table, and its stock ticker is ‘AAPL’. Delete

Image 5 - Running a Query

This image shows how the menu of queries can be ran. There is a dropdown selection of all possible reports to view. In this example, the first query is ran, and it shows all records in the ‘Company’ table and their corresponding tickers. Running a query

Image 6 - Filtering a Table

This image shows how each table can be filtered. in this example, I wanted to see all records in the Technology sector. Filtering ‘Technology’

Reports

SQL DDL

No errors detected while loading: ./my-ddl.sql

20 queries:

  1. List all companies and their stock tickers.
sql = """
SELECT Name, Ticker 
FROM Company;
""" 
run_sql_and_return_df(sql, cnx)
Name Ticker
0 Apple Inc. AAPL
1 Amazon.com Inc. AMZN
2 Alphabet Inc. GOOGL
3 Meta Platforms Inc. META
4 Microsoft Corporation MSFT
5 Nvidia Corporation NVDA
6 test test
7 Tesla Inc. TSLA
  1. Find all companies from the Technology sector.
sql = """
SELECT Name 
FROM Company 
WHERE Sector = 'Technology';
"""
run_sql_and_return_df(sql, cnx)
Name
0 Apple Inc.
1 Microsoft Corporation
2 Nvidia Corporation
  1. Get the stock ticker and end of day price for Apple.
sql = """
SELECT Stock_ticker, Eod_price 
FROM Stock 
WHERE Stock_ticker = 'AAPL';
"""
run_sql_and_return_df(sql, cnx)
Stock_ticker Eod_price
0 AAPL 242.84
  1. Find the companies with IPO dates after 2010
sql = """
SELECT 
    Company.Name, 
    Stock.IPO_date
FROM 
    Stock
JOIN 
    Company ON Stock.Stock_ticker = Company.Ticker
WHERE 
    Stock.IPO_date > '2010-01-01'
ORDER BY 
    Stock.IPO_date;
"""
run_sql_and_return_df(sql, cnx)
Name IPO_date
0 Tesla Inc. 2010-06-29
1 Meta Platforms Inc. 2012-05-18
  1. Retrieve the total revenue and net income for Apple
sql = """
SELECT Total_revenue, Net_income_common_stockholders 
FROM Income_Statement 
WHERE Stock_ticker = (SELECT Stock_ticker FROM Company WHERE Ticker = 'AAPL');
"""
run_sql_and_return_df(sql, cnx)
Total_revenue Net_income_common_stockholders
0 182527000.0 40269000.0
1 386064000.0 21331000.0
2 31536000.0 690000.0
3 85965000.0 29146000.0
4 365817000.0 94680000.0
5 168088000.0 61271000.0
6 257637000.0 76033000.0
7 469822000.0 33364000.0
8 16675000.0 4332000.0
9 53823000.0 5524000.0
10 117929000.0 39370000.0
11 394328000.0 99803000.0
12 198270000.0 72738000.0
13 282836000.0 59972000.0
14 513983000.0 -2722000.0
15 26914000.0 9752000.0
16 81462000.0 12583000.0
17 116609000.0 23200000.0
18 383285000.0 96995000.0
19 211915000.0 72361000.0
20 307394000.0 73795000.0
21 574785000.0 30425000.0
22 26974000.0 4368000.0
23 96773000.0 14999000.0
24 134902000.0 39098000.0
25 391035000.0 93736000.0
26 245122000.0 88136000.0
27 60922000.0 29760000.0
  1. Get the total debt for all companies in the Consumer Cyclical sector with fiscal year end
sql = """
SELECT 
    Company.Name, 
    Balance_Sheet.Total_debt,
    Balance_Sheet.Fiscal_year_end
FROM 
    Company 
JOIN 
    Stock ON Company.Ticker = Stock.Stock_ticker
JOIN 
    Balance_Sheet ON Stock.Stock_ticker = Balance_Sheet.Stock_ticker
WHERE 
    Company.Sector = 'Consumer Cyclical'
ORDER BY 
    Balance_Sheet.Fiscal_year_end ASC; 
"""
run_sql_and_return_df(sql, cnx)
Name Total_debt Fiscal_year_end
0 Amazon.com Inc. 84389000.0 2020-12-31
1 Tesla Inc. 13279000.0 2020-12-31
2 Amazon.com Inc. 116395000.0 2021-12-31
3 Tesla Inc. 8873000.0 2021-12-31
4 Amazon.com Inc. 140118000.0 2022-12-31
5 Tesla Inc. 5748000.0 2022-12-31
6 Amazon.com Inc. 135611000.0 2023-12-31
7 Tesla Inc. 9573000.0 2023-12-31
  1. List all companies with a gross profit greater than 50 million.
sql = """
SELECT Company.Name, Income_Statement.Gross_profit, Income_Statement.Fiscal_year_end
FROM Company
JOIN Income_Statement ON Company.Ticker = Income_Statement.Stock_Ticker
WHERE Income_Statement.Gross_profit > 50000000
ORDER BY Income_Statement.Fiscal_year_end ASC;
"""
run_sql_and_return_df(sql, cnx)
Name Gross_profit Fiscal_year_end
0 Amazon.com Inc. 51500000.0 2020-12-31
1 Meta Platforms Inc. 69273000.0 2020-12-31
2 Alphabet Inc. 97795000.0 2020-12-31
3 Microsoft Corporation 52232000.0 2021-06-30
4 Apple Inc. 152836000.0 2021-09-30
5 Alphabet Inc. 110939000.0 2021-12-31
6 Amazon.com Inc. 66315000.0 2021-12-31
7 Meta Platforms Inc. 95280000.0 2021-12-31
8 Microsoft Corporation 135620000.0 2022-06-30
9 Apple Inc. 170782000.0 2022-09-30
10 Alphabet Inc. 156633000.0 2022-12-31
11 Meta Platforms Inc. 91360000.0 2022-12-31
12 Amazon.com Inc. 67640000.0 2022-12-31
13 Microsoft Corporation 146052000.0 2023-06-30
14 Apple Inc. 169148000.0 2023-09-30
15 Alphabet Inc. 174062000.0 2023-12-31
16 Amazon.com Inc. 93805000.0 2023-12-31
17 Meta Platforms Inc. 108943000.0 2023-12-31
18 Microsoft Corporation 171008000.0 2024-06-30
19 Apple Inc. 180683000.0 2024-09-30
  1. Find the companies with total assets greater than 1 billion.
sql = """
SELECT Company.Name, Balance_Sheet.Fiscal_year_end
FROM Company
JOIN Balance_Sheet ON Company.Ticker = Balance_Sheet.Stock_ticker
WHERE Balance_Sheet.Total_assets > 1000000000
ORDER BY Balance_Sheet.Fiscal_year_end ASC;
"""
run_sql_and_return_df(sql, cnx)
Name Fiscal_year_end
0 no records returned
  1. Get the fiscal year-end date for Microsoft from 2021 to 2024.
sql = """
SELECT Company.Name, Income_Statement.Fiscal_year_end
FROM Company
JOIN Income_Statement ON Company.Ticker = Income_Statement.Stock_ticker
WHERE Company.Name = 'Microsoft Corporation'
ORDER BY Income_Statement.Fiscal_year_end;
"""
run_sql_and_return_df(sql, cnx)
Name Fiscal_year_end
0 Microsoft Corporation 2021-06-30
1 Microsoft Corporation 2022-06-30
2 Microsoft Corporation 2023-06-30
3 Microsoft Corporation 2024-06-30
  1. Find all companies that have a working capital greater than 50 million.
sql = """
SELECT Company.Name, Balance_Sheet.Working_capital
FROM Company 
JOIN Balance_Sheet ON Balance_Sheet.Stock_Ticker = Company.Ticker
WHERE Balance_Sheet.Working_capital > 50000000
ORDER BY Balance_Sheet.Working_capital
"""
run_sql_and_return_df(sql, cnx)
Name Working_capital
0 Meta Platforms Inc. 53405000.0
1 Meta Platforms Inc. 60689000.0
2 Microsoft Corporation 74602000.0
3 Microsoft Corporation 80108000.0
4 Alphabet Inc. 89716000.0
5 Alphabet Inc. 95495000.0
6 Microsoft Corporation 95749000.0
7 Alphabet Inc. 117462000.0
8 Alphabet Inc. 123889000.0
  1. Get the names of companies in the ‘Software Infrastructure’ industry.
sql = """
SELECT Name, Industry 
FROM Company 
WHERE Industry = 'Software Infrastructure';
"""
run_sql_and_return_df(sql, cnx)
Name Industry
0 Microsoft Corporation Software Infrastructure
  1. Retrieve the total revenue and cost of revenue for Tesla.
sql = """
SELECT Company.Name, Income_Statement.Total_revenue, Income_Statement.Cost_of_revenue 
FROM Company
JOIN Income_Statement ON Income_Statement.Stock_Ticker = Company.Ticker 
WHERE Company.Ticker = (SELECT Ticker FROM Company WHERE Ticker = 'TSLA');
"""
run_sql_and_return_df(sql, cnx)
Name Total_revenue Cost_of_revenue
0 Tesla Inc. 31536000.0 24906000.0
1 Tesla Inc. 53823000.0 40217000.0
2 Tesla Inc. 81462000.0 60609000.0
3 Tesla Inc. 96773000.0 79113000.0
  1. Find companies that have both net income for common stockholders greater than 10 million.
sql = """
SELECT Company.Name, Income_Statement.Net_income_common_stockholders
FROM Company
JOIN Income_Statement ON Income_Statement.Stock_Ticker = Company.Ticker 
WHERE Income_Statement.Net_income_common_stockholders > 10000000;
"""
run_sql_and_return_df(sql, cnx)
Name Net_income_common_stockholders
0 Alphabet Inc. 40269000.0
1 Amazon.com Inc. 21331000.0
2 Meta Platforms Inc. 29146000.0
3 Apple Inc. 94680000.0
4 Microsoft Corporation 61271000.0
5 Alphabet Inc. 76033000.0
6 Amazon.com Inc. 33364000.0
7 Meta Platforms Inc. 39370000.0
8 Apple Inc. 99803000.0
9 Microsoft Corporation 72738000.0
10 Alphabet Inc. 59972000.0
11 Tesla Inc. 12583000.0
12 Meta Platforms Inc. 23200000.0
13 Apple Inc. 96995000.0
14 Microsoft Corporation 72361000.0
15 Alphabet Inc. 73795000.0
16 Amazon.com Inc. 30425000.0
17 Tesla Inc. 14999000.0
18 Meta Platforms Inc. 39098000.0
19 Apple Inc. 93736000.0
20 Microsoft Corporation 88136000.0
21 Nvidia Corporation 29760000.0
  1. Retrieve the operating cashflow for Nvidia.
sql = """
SELECT Company.Name, Cash_Flow.Operating_cashflow
FROM Company
JOIN Cash_Flow ON Cash_Flow.Stock_Ticker = Company.Ticker
WHERE Company.Ticker = (SELECT Ticker FROM Company WHERE Ticker = 'NVDA');
"""
run_sql_and_return_df(sql, cnx)
Name Operating_cashflow
0 Nvidia Corporation 5822000.0
1 Nvidia Corporation 9108000.0
2 Nvidia Corporation 5641000.0
3 Nvidia Corporation 28090000.0
  1. Retreive the earnings for all tickers in the fiscal date end of 2022-12-31
sql = """
SELECT Company.Ticker, Earnings.Reported_eps, Earnings.Fiscal_date_end
FROM Company
JOIN Earnings ON Earnings.Stock_ticker = Company.Ticker
WHERE Earnings.Fiscal_date_end = '2022-12-31';
"""
run_sql_and_return_df(sql, cnx)
Ticker Reported_eps Fiscal_date_end
0 GOOGL 4.55 2022-12-31
1 AMZN 0.97 2022-12-31
2 TSLA 4.07 2022-12-31
3 META 8.58 2022-12-31
  1. Get the capital expenditures for companies which are greater than -2000000
sql = """
SELECT Company.Name, Cash_Flow.Capital_expenditures
FROM Company
JOIN Cash_Flow ON Cash_Flow.Stock_Ticker = Company.Ticker
WHERE Cash_Flow.Capital_expenditures > -2000000;
"""
run_sql_and_return_df(sql, cnx)
Name Capital_expenditures
0 Nvidia Corporation -1128000.0
1 Nvidia Corporation -976000.0
2 Nvidia Corporation -1833000.0
3 Nvidia Corporation -1069000.0
  1. Retrieve the stock ticker with fiscal year and cash dividends paid which are not null
sql = """
SELECT Company.Name, Cash_Flow.Cash_dividends_paid, Cash_Flow.Fiscal_year_end
FROM Company
JOIN Cash_Flow ON Cash_Flow.Stock_Ticker = Company.Ticker
WHERE Cash_Flow.Cash_dividends_paid IS NOT NULL
ORDER BY Cash_Flow.Fiscal_year_end
"""
run_sql_and_return_df(sql, cnx)
Name Cash_dividends_paid Fiscal_year_end
0 Nvidia Corporation -395000.0 2021-01-31
1 Microsoft Corporation -16521000.0 2021-06-30
2 Apple Inc. -14467000.0 2021-09-30
3 Nvidia Corporation -399000.0 2022-01-31
4 Microsoft Corporation -18135000.0 2022-06-30
5 Apple Inc. -14841000.0 2022-09-30
6 Nvidia Corporation -398000.0 2023-01-31
7 Microsoft Corporation -19800000.0 2023-06-30
8 Apple Inc. -15025000.0 2023-09-30
9 Nvidia Corporation -395000.0 2024-01-31
10 Microsoft Corporation -21771000.0 2024-06-30
11 Apple Inc. -15234000.0 2024-09-30
  1. Retrieve the stock ticker and free cash flow where free cash flow is greater than 50,000,000
sql = """
SELECT Stock_Ticker, Free_cash_flow
FROM Cash_Flow
WHERE Free_cash_flow > 50000000;
"""
run_sql_and_return_df(sql, cnx)
Stock_Ticker Free_cash_flow
0 AAPL 92953000.0
1 MSFT 56118000.0
2 GOOGL 67012000.0
3 AAPL 111443000.0
4 MSFT 65149000.0
5 GOOGL 60010000.0
6 AAPL 99584000.0
7 MSFT 59475000.0
8 GOOGL 69495000.0
9 AAPL 108807000.0
10 MSFT 74071000.0
  1. Retrieve the fiscal year and operating cash flow for records where operating cash flow is less than 10,000,000.
sql = """
SELECT Fiscal_year_end, Operating_cashflow
FROM Cash_Flow
WHERE Operating_cashflow < 10000000;
"""
run_sql_and_return_df(sql, cnx)
Fiscal_year_end Operating_cashflow
0 2020-12-31 5943000.0
1 2021-01-31 5822000.0
2 2022-01-31 9108000.0
3 2023-01-31 5641000.0
  1. Retrieve the stock ticker, fiscal year, and capital expenditures for records where capital expenditures are negative.
sql = """
SELECT Stock_Ticker, Capital_expenditures, Fiscal_year_end
FROM Cash_Flow
WHERE Capital_expenditures < 0;
"""
run_sql_and_return_df(sql, cnx)
Stock_Ticker Capital_expenditures Fiscal_year_end
0 GOOGL -22281000.0 2020-12-31
1 AMZN -40140000.0 2020-12-31
2 TSLA -3242000.0 2020-12-31
3 META -15163000.0 2020-12-31
4 AAPL -11085000.0 2021-09-30
5 MSFT -20622000.0 2021-06-30
6 GOOGL -24640000.0 2021-12-31
7 AMZN -61053000.0 2021-12-31
8 NVDA -1128000.0 2021-01-31
9 TSLA -8014000.0 2021-12-31
10 META -18690000.0 2021-12-31
11 AAPL -10708000.0 2022-09-30
12 MSFT -23886000.0 2022-06-30
13 GOOGL -31485000.0 2022-12-31
14 AMZN -63645000.0 2022-12-31
15 NVDA -976000.0 2022-01-31
16 TSLA -7172000.0 2022-12-31
17 META -31431000.0 2022-12-31
18 AAPL -10959000.0 2023-09-30
19 MSFT -28107000.0 2023-06-30
20 GOOGL -32251000.0 2023-12-31
21 AMZN -52729000.0 2023-12-31
22 NVDA -1833000.0 2023-01-31
23 TSLA -8899000.0 2023-12-31
24 META -27266000.0 2023-12-31
25 AAPL -9447000.0 2024-09-30
26 MSFT -44477000.0 2024-06-30
27 NVDA -1069000.0 2024-01-31